package com.apestech.framework.sql;

import com.apestech.formwork.xml.XmlQuery;
import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.sql.condition.ConditionMap;
import com.apestech.framework.sql.condition.ConditionSql;
import com.apestech.framework.sql.condition.QueryField;
import com.apestech.framework.sql.modul.FromList;
import com.apestech.framework.sql.modul.SqlList;
import com.apestech.framework.sql.table.TableSql;
import com.apestech.framework.sql.variable.SqlCase;
import com.apestech.framework.xml.plugin.XmlPaste;
import com.apestech.framework.xml.plugin.modul.FromField;
import com.apestech.framework.xml.plugin.modul.GridHead;
import com.apestech.framework.xml.plugin.modul.QryConfig;
import com.apestech.framework.xml.sql.SqlNodeVar;

import java.util.ArrayList;

public class SqlParam extends SqlUtil{

	private SqlSession session;
	private String xmlsql=null;
	private ConditionMap param=null;
	private SqlList sqlparam=null;
	private SqlNodeVar.SqlType type;
	private FromList fromtab;
	private XmlPaste xml;
	private String countSql;
	private String sumSql;
	private QueryField.Sql execSql;
	private QueryField.Sql pageSql;
	private String timestampField;
	private XmlQuery xmlQuery;

	public SqlParam(SqlSession session){
		this.session=session;
	}
	
	public void setSqlInput(JSONObject input, QryConfig qryConfig) throws Exception{
		param=null;
		init(input, qryConfig);
	}

	private void init(JSONObject input,QryConfig qryConfig) throws Exception {
		this.xmlsql=null;
		this.execSql=new QueryField.Sql();
		iniInput(input, qryConfig);
		setSql();
	}

	public void setSession(SqlSession session) {
		this.session = session;
	}

	public SqlSession getSession() {
		return session;
	}

	private void iniInput(JSONObject input, QryConfig qryConfig) throws Exception{
		if(param==null) param=new ConditionMap(input);
		param.setQryConfig(qryConfig);
		param.getData(input);
	}

	public void setParam(ConditionMap param) {
		this.param = param;
	}

	public String getTimestampField() {
		return timestampField;
	}

	public void setTimestampField(String timestampField) {
		this.timestampField = timestampField;
	}

	public boolean isPrintSql(){
		if(param!=null) return param.isPrintSql();
		return true;
	}

	public String getPrintSql(){
		if(pageSql==null) return execSql.getPrintSql();
		else return pageSql.getPrintSql();
	}
	
	public String getExecSql(){
		if(pageSql==null) return execSql.getExecSql();
		else return pageSql.getExecSql();
	}

	public JSONArray getParams(){
		if(pageSql==null) return this.execSql.getResParam();
		else return pageSql.getResParam();
	}

	public String getXmlsql() {
		return xmlsql;
	}

	public String getSql(){
		return this.xmlsql;
	}

	public FromList getFromtab() {
		return fromtab;
	}

	public ConditionMap getParam() {
		return param;
	}

	public XmlQuery getXmlQuery() {
		return xmlQuery;
	}

	public XmlPaste getXml() {
		return xml;
	}

	public void setXmlQuery(XmlQuery xmlQuery) {
		this.xmlQuery = xmlQuery;
		if(session==null) session=xmlQuery.getSession();
	}

	private void setSql() throws Exception {
		if(param.getNodeid()!=null && param.getNodeName()!=null){
			setXmlId(param.getNodeid(), param.getNodeName());
		} else if(param.getSql()!=null){
			setSql(param.getSql(), param.getFrom(), SqlNodeVar.SqlType.SQL);
		} else if(param.getXmlSql()!=null){
			setSql(param.getXmlSql(), param.getFrom(), SqlNodeVar.SqlType.XML_SQL);
		} else if(param.getTable()!=null){
			setTableSql(param.getTable());
		}
		if(xml!=null){
			if(param.gettableId()==null){
				xmlsql=xml.getSql();
				sqlparam=xml.getParam();
			}
		}
		if(xmlsql==null){
			throw new Exception("执行SQL不允许为空，请检查入参配置！");
		}
		if(param.isAutoCompletion()){
			param.setFromtab(fromtab);
			getSqlInfo(xmlsql);
			if(fromtab.size()==0){
				throw new Exception("SQL结构太复杂，请配置nb_from入参信息！");
			}
		}
//		if(xmlsql.indexOf(SqlNodeVar.getWhereStr())>=0){
//			param.setAutoCompletion("1");
//		}
	}

	private void setTableSql(String tabName) throws Exception {
		TableSql tabSql=new TableSql(tabName, session, param);
		xmlsql=tabSql.getSql();
		sqlparam=tabSql.getSqlparam();
		fromtab=tabSql.getFromtab();
	}

	/**
	 * SQL直接写SQL方式传入，支持@WHERE@，要求传入FROM部分内容; XML_SQL传入SQL存在公式解析，这种模式将会走XML SQL解析，语法要求同MYBATIS
	 * @param sql
	 * @param fromtab
	 */
	private void setSql(String sql, String fromtab, SqlNodeVar.SqlType type) throws Exception {
		this.type=type;
		String s=iniSql(sql);
		if(s.indexOf("/>")>=0) type=SqlNodeVar.SqlType.XML_SQL;
		xmlsql="<"+param.getSqlType()+">"+iniSql(sql)+"</"+param.getSqlType()+">";
		xml=new XmlPaste(xmlsql);
		xml.setSession(session);
		xml.expainSql(sql, type, fromtab);
		//xml.setSqlNode(true);
		//xml.expain();
		setXMLVar(xml);
	}

	/**
	 * xml id方式调用
	 * @param xmlId
	 * @throws Exception
	 */
	private void setXmlId(String xmlId, String nodetype) throws Exception {
		this.type=SqlNodeVar.SqlType.XML;
		if(xmlQuery==null) xml=new XmlPaste(xmlId, nodetype, session);
		else xml=new XmlPaste(xmlId, nodetype, xmlQuery);
		xml.expain();
		if(param.gettableId()!=null){
			param.setSqlType(xml.getSqlType());
			param.setSqlrule(xml.getSqlrule());
			param.setRule(xml.getRule());
			param.setFromtab(new FromList(param.getFrom(), null, session));
			param.setExecSql(xml.getExecSql(param));
			TableSql tabSql=new TableSql(null, session, param);
			xmlsql=tabSql.getSql();
			sqlparam=tabSql.getSqlparam();
		} else setXMLVar(xml);
		if(xml.getAutoCompletion()!=null) param.setAutoCompletion(xml.getAutoCompletion());
	}

	private void setXMLVar(XmlPaste xml) throws Exception {
		xmlsql=xml.getExecSql(param);
		if(param.isAutoCompletion()){
			if(fromtab==null){
				if(param.getFrom()!=null){
					fromtab=new FromList(param.getFrom(), null, session);
				} else {
					fromtab=xml.getFromtab();
				}
			}
		}
		param.setRule(xml.getRule());
		param.setRole(xml.getAuths());
		param.setHtw(xml.getHtw());
		if(xml.getHtw()!=null){
			if(xml.getHtw().getGridHead()==null) {
				xml.getHtw().setGridHead(xml.getHead());
			}
			param.setHtw(xml.getHtw());
		}
		else param.setHead(xml.getHead());
		param.setSqlrule(xml.getSqlrule());
		param.setOtherControl(xml.getOther());
	}

	public void expain() throws Exception{
		if(param.isAutoCompletion() ){
			param.setFromtab(fromtab);
			param.getOtherSql();
			param.setDbType(session.getDbType());
			ConditionSql query=new ConditionSql(param, fromtab);
			this.param=query.expain();
			String s=query.getSql();
			if(s==null) s="";
			String wkey=leftStr(SqlNodeVar.getWhereStr(), SqlNodeVar.getWhereStr().length()-1);
			int pos=this.xmlsql.indexOf(wkey);
			if(pos>=0){
				int zlen=this.sqlparam.size();
				boolean zflag=false;
				for(int z=0; z<zlen; z++){   //关键字要顺序处理，包括主KEY也一样
					String text=this.sqlparam.get(z).getText();
					pos=text.indexOf(wkey);
					if(pos>=0){
						String zkey=SqlNodeVar.getWhereStr();
						String rkey=getWhereKey(text, -1);
						JSONObject froms=param.getRule().getFroms();
						int rpos=text.indexOf(rkey);
						if(rpos>0){
							this.sqlparam.get(z).setText("");
							if(rkey.equals(zkey)){
								this.xmlsql=this.xmlsql.replace(rkey, s);
								this.sqlparam.addAll(z,query.getSqlparam());
								zflag=true;
							} else {
								String no=rkey.substring(rkey.indexOf("_")+1, rkey.length()-1);
								FromField fromf= (FromField) froms.get("from"+no);
								ConditionSql rquery=new ConditionSql(param, fromf.getFromtab());
								rquery.setSub(true);
								this.param=rquery.expain();
								String rs=rquery.getSql();
								if(rs==null){
									rs="";
								}
								this.xmlsql=this.xmlsql.replace(rkey, rs);
								this.sqlparam.addAll(z,rquery.getSqlparam());
								zlen=this.sqlparam.size();
							}
						}
//						this.sqlparam.add(text);
					}
				}
				if(!zflag){
					this.xmlsql=this.xmlsql+s;
					this.sqlparam.addAll(query.getSqlparam());
				}
			} else {
				if(this.xmlsql.indexOf("INSERT")<0){
					this.xmlsql=this.xmlsql+s;
					this.sqlparam.addAll(query.getSqlparam());
				}
			}
		}
		SqlCase sqlcase=new SqlCase(sqlparam, session.getDbType(), param.getSqlType());
		sqlcase.expain();
		MultiDataBase db=new MultiDataBase(session.getDbType());
		String s=getSqlExpress(sqlcase.getExecSql());
		String p=getSqlExpress(sqlcase.getPrintSql());
		ArrayList<String> hlist=null;
		if("select".equals(param.getSqlType())){
			String s1=getColumnSql(s);
			hlist=session.getSqlColumn(s1);
			if(param.getHtw()!=null){
				GridHead gridhead=param.getHtw().getGridHead();
				if(gridhead==null) gridhead=new GridHead();
				if(gridhead.getHeadjson()==null) gridhead.addHead(hlist);
				param.getHtw().setGridHead(gridhead);
				String sline=db.htwLine(param.getHtw(), s);
				session.getCn().query(sline, sqlcase.getParamList(), new QueryCallBackHandler() {
					@Override
					public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
						param.getHtw().addPivotHead(rs);
					}
				}, param, param.getTimeField());
				s=db.getHtwFormatSql(hlist, s, param.getHtw(), param.getFormat());
				p=db.getHtwFormatSql(hlist, p, param.getHtw(), param.getFormat());
				s=db.htw(param.getHtw(), s);
				p=db.htw(param.getHtw(), p);
			}
		}
		execSql.setExecSql(s);
		execSql.setPrintSql(p);
		execSql.setResParam(sqlcase.getParamList());
		if("select".equals(param.getSqlType())){
			if(param.getFormat()!=null){
				sumSql=db.getSumSql(execSql.getExecSql(), param.getFormat(), param.getHtw());
			}
			boolean forwhile="forwhile".equals(param.getPageType());
			boolean fast=!"order".equals(param.getRule().getPage());
			if(forwhile && param.getPage()==1){
				execSql.setExecSql(db.getPageSql(db.getOrderSql(s, param.getOrderField(), param.getOrderType()), fast, forwhile));
			}
			if((("query").equals(param.getPageType()) && param.getPage()>0)||
                    (forwhile && param.getPage()>1)){
				pageSql=new QueryField.Sql();
				if(param.getHtw()==null){
					s=db.getExecFormatSql(hlist, s, param.getFormat());
					p=db.getExecFormatSql(hlist, p, param.getFormat());
				}
				pageSql.setExecSql(db.getPageSql(db.getOrderSql(s, param.getOrderField(), param.getOrderType()), fast, forwhile));
				pageSql.setPrintSql(db.getPagePrintSql(db.getOrderSql(p, param.getOrderField(), param.getOrderType()), fast, param.getStartPage(), param.getEndPage()));
				pageSql.setResParam(db.getPageParam(execSql.getResParam(), param.getStartPage(), param.getEndPage()));
				pageSql.setSumSql(db.getSumSql(db.getPageSql(execSql.getExecSql(), fast, forwhile), param.getFormat(), param.getHtw()));
				countSql=db.getCountSql(execSql.getExecSql());
			}
			if(countSql==null && param.isXls()) countSql=db.getCountSql(execSql.getExecSql());
			if(!"".equals(param.getOrderField())){
				execSql.setExecSql(db.getOrderSql(execSql.getExecSql(), param.getOrderField(), param.getOrderType()));
				execSql.setPrintSql(db.getOrderSql(execSql.getPrintSql(), param.getOrderField(), param.getOrderType()));
			}
		}
	}

	private void getSqlInfo(String sql) throws Exception{
		String from=param.getFrom();
		if(param.getSqlType()==null || from==null){
			SqlPaste sqlpaste=new SqlPaste(sql);
			if(param.getSqlType()==null) param.setSqlType(sqlpaste.getSqlType().toLowerCase());
			from=sqlpaste.getTableName();
		}
		if(fromtab==null){
			if(from==null){
				throw new Exception("请配置nb_from入参信息!");
			}
			fromtab=new FromList(from, sql, session);
		}
		if(param.getOrderSql()!=null){
			String text=param.getOrderSql();
			xmlsql=xmlsql+text;
			String rkey=getWhereKey(text, -1);
			if(rkey!=null){
				int pos=text.indexOf(rkey);
				String other=text.substring(0, pos);
				this.sqlparam.add(" "+other);
				this.sqlparam.add(" "+rkey);
				other=text.substring(pos+rkey.length(), text.length());
				this.sqlparam.add(" "+other);
			} else {
				this.sqlparam.add(" "+text);
			}
		}
	}

	private String getSqlExpress(String text){
		if(type==SqlNodeVar.SqlType.XML_SQL){
			text = finiSql(text);
		}
		return text;
	}

	public String getCountSql() {
		return countSql;
	}

	public String getSumSql() {
		return sumSql;
	}

	public JSONArray getOParam(){
		return execSql.getResParam();
	}

	public QueryField.Sql getPageSql() {
		return pageSql;
	}
}
